package edu.wtbu.dao;

import java.util.List;

import java.util.HashMap;

import edu.wtbu.helper.MySqlHelper;

public class ScheduleDao {
	public static List<HashMap<String, Object>> findScheduleByDate(String start, String end, int startPage, int pageSize) {
		String sql = "select  s1.scheduleIdd as ScheduleId,\r\n" + "FlightNumber,\r\n" + "Gate,\r\n" + "Date,\r\n"
				+ "Time,\r\n" + "EconomyPrice,\r\n" + "route.flighttime as FlightTime,\r\n"
				+ "departcity.citycode as DepartCityCode,\r\n" + "departcity.cityname as DepartCityName,\r\n"
				+ "arriveairport.citycode as ArriveCityCode,\r\n" + "arrivecity.cityname as ArriveCityName,\r\n"
				+ "FlightStatus.ActualArrivalTime as ActualArrivalTime  \r\n" + "from `schedule` as s1\r\n"
				+ "left join route on s1.RouteId=route.RouteId \r\n"
				+ "LEFT JOIN airport as departairport on route.DepartureAirportIATA=departairport.IATACode \r\n"
				+ "left join airport as arriveairport on route.ArrivalAirportIATA = arriveairport.IATACode \r\n"
				+ "left join city as departcity on departcity.citycode=departairport.citycode \r\n"
				+ "left join city as arrivecity on arrivecity.citycode = arriveairport.citycode  left join FlightStatus "
				+ "on FlightStatus.scheduleId=s1.scheduleId"
				+ " where s1.date between ? and ?  limit ?,?;";
		return MySqlHelper.executeQueryReturnMap(sql, new Object[] { start, end, startPage * pageSize, pageSize });
	}

	public static int scheduleByDateCount(String start, String end) {
		String sql = "select count(*) as total " + "from `schedule` as s1\r\n"
				+ "left join route on s1.RouteId=route.RouteId \r\n"
				+ "LEFT JOIN airport as departairport on route.DepartureAirportIATA=departairport.IATACode \r\n"
				+ "left join airport as arriveairport on route.ArrivalAirportIATA = arriveairport.IATACode \r\n"
				+ "left join city as departcity on departcity.citycode=departairport.citycode \r\n"
				+ "left join city as arrivecity on arrivecity.citycode = arriveairport.citycode  left join FlightStatus on FlightStatus.scheduleId=s1.scheduleId"
				+ " where s1.date between ? and ?;";
		List<HashMap<String, Object>> scheduleList = MySqlHelper.executeQueryReturnMap(sql,
				new Object[] { start, end });

		if (scheduleList != null && scheduleList.size() > 0) {
			return Integer.parseInt(scheduleList.get(0).get("total").toString());
		}
		return 0;
	}

	public static List<HashMap<String, Object>> findScheduleByCityAndDate(String fromCity, String toCity,
			String startDate, String endDate) {
		String sql="select Status,arriveCity.CityName as ArriveCityName,departCity.CityName as DepartCityName,"
				+ "departCity.CityCode as DepartCityCode,Gate,Time,arriveCity.CityCode as ArriveCityCode,s1.ScheduleId,Date,"
				+ "FlightTime,Name,FlightNumber,EconomyPrice from schedule as s1 left join route on s1.RouteId=route.RouteId"
				+ " left join airport as departAirport on route.DepartureAirportIATA=departAirport.IATACode left join City "
				+ "as departCity on departAirport.CityCode=departCity.CityCode left join airport as arriveAirport"
				+ " on route.ArrivalAirportIATA=arriveAirport.IATACode left join city as arriveCity on "
				+ "arriveAirport.CityCode=arriveCity.CityCode left join aircraft on s1.AircraftId= aircraft.AircraftId"
				+ " where departCity.CityName=? and arriveCity.CityName=? and (Date between ? and ?)";
		return MySqlHelper.executeQueryReturnMap(sql, new Object[] { fromCity, toCity, startDate, endDate });
	}

	public static int updateSchedule(int scheduleId, String status) {
		String sql = "update Schedule set status=?" + " where ScheduleId=?";
		return MySqlHelper.executeUpdate(sql, new Object[] { status, scheduleId });		
	}

	public static List<HashMap<String, Object>> findScheduleByScheduleId(int scheduleId) {
		String sql = "select  s1.*,route.*,Aircraft.*\r\n" + "from `schedule` as s1\r\n"
				+ "left join route on s1.RouteId=route.RouteId\r\n"
				+ "left join Aircraft on Aircraft.AircraftId=s1.AircraftId\r\n" + "where scheduleId =?;";
		return MySqlHelper.executeQueryReturnMap(sql, new Object[] { scheduleId });
	}

	public static List<HashMap<String, Object>> findTicketsByScheduleId(int scheduleId) {
		String sql = "SELECT ScheduleId, FlightReservation.CabinTypeId, cabintype.CabinTypeName,count(*)AS Counts\r\n"
				+ "FROM FlightReservation\r\n"
				+ "left join  cabintype on cabintype.cabintypeId=FlightReservation.cabintypeId\r\n"
				+ "where ScheduleId=? GROUP BY CabinTypeId ;";
		return MySqlHelper.executeQueryReturnMap(sql, new Object[] { scheduleId });
	}

	public static List<HashMap<String, Object>> findSeatByScheduleId(int scheduleId) {
		String sql = "select f.CabinTypeId,RowNumber,ColumnName,AircraftId from FlightReservation as f\r\n"
				+ "left join SeatLayout on SeatLayout.id=f.SeatLayoutId \r\n" + "where ScheduleId=?;";
		return MySqlHelper.executeQueryReturnMap(sql, new Object[] { scheduleId });
	}

	public static List<HashMap<String, Object>> findSeatByAircraftId(int aircraftId) {
		String sql = "select * from seatlayout where AircraftId=?;";
		return MySqlHelper.executeQueryReturnMap(sql, new Object[] { aircraftId });
	}

	public static List<HashMap<String, Object>> findSchedulesByCityAndDate(String fromCity, String toCity,
			String startDepartureDate, String endDepartureDate) {
		String sql = "select  \r\n" + "ScheduleId,\r\n" + "Date,\r\n"
				+ "DATE_ADD(date,INTERVAL (route.flighttime) MINUTE) as PreArrivalTime,\r\n" + "Time,\r\n"
				+ "schedule.FlightNumber,\r\n" + "route.flighttime as FlightTime,\r\n"
				+ "departcity.citycode as DepartCityCode,\r\n" + "departcity.cityname as DepartCityName,\r\n"
				+ "arriveairport.citycode as ArriveCityCode,\r\n" + "arrivecity.cityname as ArriveCityName,\r\n"
				+ "EconomyPrice,\r\n" + "FirstSeatsAmount,\r\n" + "BusinessSeatsAmount,\r\n" + "EconomySeatsAmount,\r\n"
				+ "delay.DelayCount,\r\n" + "delay.NotDelay,\r\n" + "delay.AllCount\r\n" + "from `schedule` \r\n"
				+ "left join route on schedule.RouteId=route.RouteId \r\n"
				+ "LEFT JOIN airport as departairport on route.DepartureAirportIATA=departairport.IATACode \r\n"
				+ "left join airport as arriveairport on route.ArrivalAirportIATA = arriveairport.IATACode \r\n"
				+ "left join city as departcity on departcity.citycode=departairport.citycode \r\n"
				+ "left join city as arrivecity on arrivecity.citycode = arriveairport.citycode \r\n"
				+ "left join Aircraft on Aircraft.AircraftId=schedule.AircraftId\r\n"
				+ "left join (select allFlight.FlightNumber as FlightNumber ,\r\n"
				+ "allFlight.allcount as allcount,\r\n"
				+ "delayFlight.delaycount as delaycount ,(allcount-delaycount) as notdelay\r\n" + "from \r\n"
				+ "(select schedule.FlightNumber as FlightNumber,count(*) as allcount \r\n" + "from schedule  \r\n"
				+ "where TIMEDIFF(?,DATE_ADD(schedule.date,INTERVAL (720) MINUTE)) <0  \r\n"
				+ "group by FlightNumber ) as allFlight\r\n" + "left join \r\n" + "(select \r\n"
				+ "schedule.FlightNumber as FlightNumber,\r\n" + "count(*) as delaycount\r\n" + "from schedule \r\n"
				+ "left join route on route.routeid=schedule.routeid \r\n"
				+ "left join flightstatus on flightstatus.scheduleId = schedule.scheduleid \r\n"
				+ "where TIMESTAMPDIFF(DAY,schedule.date,?) <30 \r\n"
				+ "and (TIMEDIFF(DATE_ADD(schedule.date,INTERVAL (route.flighttime+15) MINUTE),flightstatus.ActualArrivalTime) <0 or schedule.status <>'Confirmed')\r\n"
				+ "group by schedule.FlightNumber) as delayFlight on allFlight.FlightNumber=delayFlight.FlightNumber\r\n"
				+ ") as delay on delay.FlightNumber = schedule.FlightNumber\r\n"
				+ "where departcity.cityname=? and arrivecity.cityname=? \r\n" + "and  date between ? and ? ;";
		return MySqlHelper.executeQueryReturnMap(sql, new Object[] { startDepartureDate, startDepartureDate, fromCity,
				toCity, startDepartureDate, endDepartureDate });
	}

	public static List<HashMap<String, Object>> findResidueTickesByScheduleIdAndCabinType(int scheduleId,
			int cabinType) {
		String sql = "SELECT  count(*) AS Counts\r\n" + "FROM FlightReservation where ScheduleId=? and cabintypeId=?;";
		return MySqlHelper.executeQueryReturnMap(sql, new Object[] { scheduleId, cabinType });
	}

	public static List<HashMap<String, Object>> findOnestopSchedulesByCityAndDate(String fromCity, String toCity,
			String startDepartureDate, String endDepartureDate) {
		String sql = "select  \r\n" + "s1.scheduleId as S1ScheduleId,\r\n" + "s1.date as S1Date,\r\n"
				+ "DATE_ADD(s1.date,INTERVAL (route.flighttime) MINUTE) as S1PreArrivalTime,\r\n"
				+ "s1.time as S1Time,\r\n" + "s1.EconomyPrice as S1EconomyPrice,\r\n"
				+ "s1.flightnumber as S1FlightNumber,\r\n" + "route.flighttime as S1FlightTime,\r\n"
				+ "departcity.citycode as S1DepartCityCode,\r\n" + "departcity.cityname as S1DepartCityName,\r\n"
				+ "arriveairport.citycode as S1ArriveCityCode,\r\n" + "arrivecity.cityname as S1ArriveCityName, \r\n"
				+ "Aircraft.FirstSeatsAmount as S1FirstSeatsAmount,\r\n"
				+ "Aircraft.BusinessSeatsAmount as S1BusinessSeatsAmount,\r\n"
				+ "Aircraft.EconomySeatsAmount as S1EconomySeatsAmount,\r\n" + "s2.scheduleId as S2ScheduleId,\r\n"
				+ "s2.date as S2Date,\r\n" + "s2.preArrivalTime as S2PreArrivalTime,\r\n" + "s2.time as S2Time,\r\n"
				+ "s2.EconomyPrice as S2EconomyPrice,\r\n" + "s2.flightnumber as S2FlightNumber,\r\n"
				+ "s2.flighttime as S2FlightTime,\r\n" + "s2.departcitycode as S2DepartCityCode,\r\n"
				+ "s2.departcityname as S2DepartCityName,\r\n" + "s2.arrivecitycode as S2ArriveCityCode,\r\n"
				+ "s2.arrivecityname as S2ArriveCityName,\r\n" + "s2.FirstSeatsAmount as S2FirstSeatsAmount,\r\n"
				+ "s2.BusinessSeatsAmount as S2BusinessSeatsAmount,\r\n"
				+ "s2.EconomySeatsAmount as S2EconomySeatsAmount \r\n" + "from `schedule` as s1\r\n"
				+ "left join route on s1.RouteId=route.RouteId \r\n"
				+ "LEFT JOIN airport as departairport on route.DepartureAirportIATA=departairport.IATACode \r\n"
				+ "left join airport as arriveairport on route.ArrivalAirportIATA = arriveairport.IATACode \r\n"
				+ "left join city as departcity on departcity.citycode=departairport.citycode \r\n"
				+ "left join city as arrivecity on arrivecity.citycode = arriveairport.citycode\r\n"
				+ "left join Aircraft on Aircraft.AircraftId=s1.AircraftId\r\n" + "left join \r\n" + "(\r\n"
				+ "select  \r\n" + "scheduleId,\r\n" + "date,\r\n"
				+ "DATE_ADD(date,INTERVAL (route.flighttime) MINUTE) as preArrivalTime,\r\n" + "time,\r\n"
				+ "EconomyPrice,\r\n" + "flightnumber,\r\n" + "route.flighttime as flighttime,\r\n"
				+ "departcity.citycode as departcitycode,\r\n" + "departcity.cityname as departcityname,\r\n"
				+ "arriveairport.citycode as arrivecitycode,\r\n" + "arrivecity.cityname as arrivecityname,\r\n"
				+ "FirstSeatsAmount,\r\n" + "BusinessSeatsAmount,\r\n" + "EconomySeatsAmount\r\n"
				+ "from `schedule` \r\n" + "left join route on schedule.RouteId=route.RouteId \r\n"
				+ "LEFT JOIN airport as departairport on route.DepartureAirportIATA=departairport.IATACode \r\n"
				+ "left join airport as arriveairport on route.ArrivalAirportIATA = arriveairport.IATACode \r\n"
				+ "left join city as departcity on departcity.citycode=departairport.citycode \r\n"
				+ "left join city as arrivecity on arrivecity.citycode = arriveairport.citycode\r\n"
				+ "left join Aircraft on Aircraft.AircraftId=schedule.AircraftId\r\n"
				+ ")as s2 on s2.departcityname=departcity.cityname\r\n"
				+ "where departcity.cityname=? and s2.arrivecityname=? \r\n" + "and  s1.date between ? and ?\r\n"
				+ "and TIMEDIFF(DATE_ADD(s1.date,INTERVAL (route.flighttime+120) MINUTE),s2.date) <0\r\n"
				+ "and TIMEDIFF(DATE_ADD(s1.date,INTERVAL (route.flighttime+1000) MINUTE),s2.date) >0 ;";
		return MySqlHelper.executeQueryReturnMap(sql,
				new Object[] { fromCity, toCity, startDepartureDate, endDepartureDate });
	}

	public static List<HashMap<String, Object>> findFlightNumber(String startDepartureDate) {
		String sql = "select allFlight.FlightNumber as FlightNumber ,\r\n" + "allFlight.allcount as AllCount,\r\n"
				+ "delayFlight.delaycount as DelayCount ,(allcount-delaycount) as NotDelay\r\n" + "from \r\n"
				+ "(select schedule.FlightNumber as FlightNumber,count(*) as allcount \r\n" + "from schedule  \r\n"
				+ "where TIMEDIFF(?,DATE_ADD(schedule.date,INTERVAL (720) MINUTE)) <0  \r\n"
				+ "group by FlightNumber ) as allFlight\r\n" + "left join \r\n" + "(select \r\n"
				+ "schedule.FlightNumber as FlightNumber,\r\n" + "count(*) as delaycount\r\n" + "from schedule \r\n"
				+ "left join route on route.routeid=schedule.routeid \r\n"
				+ "left join flightstatus on flightstatus.scheduleId = schedule.scheduleid \r\n"
				+ "where TIMESTAMPDIFF(DAY,schedule.date,?) <30 \r\n"
				+ "and (TIMEDIFF(DATE_ADD(schedule.date,INTERVAL (route.flighttime+15) MINUTE),flightstatus.ActualArrivalTime) <0 or schedule.status <>'Confirmed')\r\n"
				+ "group by schedule.FlightNumber) as delayFlight on allFlight.FlightNumber=delayFlight.FlightNumber;";
		return MySqlHelper.executeQueryReturnMap(sql, new Object[] { startDepartureDate, startDepartureDate });

	}

	public static List<HashMap<String, Object>> findScheduleNoStop(String fromCity, String toCity,
			String startDepartureDate, String endDepartureDate) {
		// TODO Auto-generated method stub
		String sql="SELECT\r\n" + 
				"	date_format(Date,'%Y-%m-%d %H:%i:%s') as Date,\r\n" + 
				"	date_format(Time,'%H:%i:%s') as Time,\r\n" + 
				"	FlightNumber,\r\n" + 
				"	FlightTime,\r\n" + 
				"	FirstSeatsAmount,\r\n" + 
				"	BusinessSeatsAmount,\r\n" + 
				"	EconomySeatsAmount,\r\n" + 
				"	EconomyPrice,\r\n" + 
				"	date_format(DATE_ADD(\r\n" + 
				"		Date,\r\n" + 
				"		INTERVAL (route.FlightTime) MINUTE\r\n" + 
				"	),'%Y-%m-%d %H:%i:%s') AS PreArrivalTime,\r\n" + 
				"	departCity.CityName AS DepartCityName,\r\n" + 
				"	departCity.CityCode AS DepartCityCode,\r\n" + 
				"	arriveCity.CityName AS ArriveCityName,\r\n" + 
				"	arriveCity.CityCOde AS ArriveCityCode,\r\n" + 
				"	scheduleId\r\n" + 
				"FROM\r\n" + 
				"	schedule\r\n" + 
				"LEFT JOIN route ON schedule .RouteId = route.RouteId\r\n" + 
				"LEFT JOIN airport AS departAirport ON route.DepartureAirportIATA = departAirport.IATACode\r\n" + 
				"LEFT JOIN city AS departCity ON departAirport.CityCode = departCity.CityCode\r\n" + 
				"LEFT JOIN airport AS arriveAirport ON route.ArrivalAirportIATA = arriveAirport.IATACode\r\n" + 
				"LEFT JOIN city AS arriveCity ON arriveAirport.CityCode = arriveCity.CityCode\r\n" + 
				"LEFT JOIN aircraft ON schedule .AircraftId = aircraft.AircraftId\r\n" + 
				"WHERE\r\n" + 
				"	departCity.CityName = ?\r\n" + 
				"AND arriveCity.CityName = ?\r\n" + 
				"AND (\r\n" + 
				"	Date BETWEEN ?\r\n" + 
				"	AND ?\r\n" + 
				")";
		List<HashMap<String, Object>> listSchedule=MySqlHelper.executeQueryReturnMap(sql, new Object[] {fromCity,toCity,startDepartureDate,endDepartureDate});
		return listSchedule;
	}

	public static int findAllCount(String flightNumber, String startDepartureDate) {
		// TODO Auto-generated method stub
		String sql="select count(*) AS Allcount from schedule where FlightNumber=? and TIMESTAMPDIFF(DAY,DATE(Date),DATE(?))>0 and "
				+ "TIMESTAMPDIFF(DAY,DATE(Date),DATE(?))<30 and Status='Confirmed'";
		List<HashMap<String, Object>> Allcount=MySqlHelper.executeQueryReturnMap(sql, new Object[] {flightNumber,startDepartureDate,startDepartureDate});
		return Integer.parseInt(Allcount.get(0).get("Allcount").toString());
	}

	public static int findNotdelay(String flightNumber, String startDepartureDate) {
		// TODO Auto-generated method stub
		String sql="select count(*) as Notdelay from schedule LEFT JOIN route on `schedule`.RouteId=route.RouteId LEFT JOIN flightstatus on"
				+ " `schedule`.ScheduleId=flightstatus.ScheduleId where FlightNumber=? and TIMESTAMPDIFF(DAY,DATE(Date),DATE(?))>0 "
				+ "and TIMESTAMPDIFF(DAY,DATE(Date),DATE(?))<30 and ActualArrivalTime<=DATE_ADD(Date,INTERVAL(route.FlightTime)MINUTE) and "
				+ "Status='Confirmed'";
		List<HashMap<String, Object>> Notdelay=MySqlHelper.executeQueryReturnMap(sql, new Object[] {flightNumber,startDepartureDate,startDepartureDate});
		return Integer.parseInt(Notdelay.get(0).get("Notdelay").toString());
	}

	public static int findTicketCounts(int scheduleId, int cabinType) {
		// TODO Auto-generated method stub
		String sql="SELECT count(*) as counts FROM flightreservation WHERE ScheduleId=? and CabinTypeId=?";
		List<HashMap<String, Object>> ticketCounts=MySqlHelper.executeQueryReturnMap(sql, new Object[] {scheduleId,cabinType});
		return Integer.parseInt(ticketCounts.get(0).get("counts").toString());
	}

	public static List<HashMap<String, Object>> findScheduleOneStop(String fromCity, String toCity,
			String startDepartureDate, String endDepartureDate) {
		// TODO Auto-generated method stub
		String sql="SELECT\r\n" + 
				"	*\r\n" + 
				"FROM\r\n" + 
				"	(\r\n" + 
				"		SELECT\r\n" + 
				"			date_format(Date,'%Y-%m-%d %H:%i:%s') AS S1Date,\r\n" + 
				"			date_format(Time,'%H:%i:%s') AS S1Time,\r\n" + 
				"			FlightNumber AS S1FlightNumber,\r\n" + 
				"			FlightTime AS S1FlightTime,\r\n" + 
				"			FirstSeatsAmount AS S1FirstSeatsAmount,\r\n" + 
				"			BusinessSeatsAmount AS S1BusinessSeatsAmount,\r\n" + 
				"			EconomySeatsAmount AS S1EconomySeatsAmount,\r\n" + 
				"			EconomyPrice AS S1EconomyPrice,\r\n" + 
				"			date_format(DATE_ADD(\r\n" + 
				"				Date,\r\n" + 
				"				INTERVAL (route.FlightTime) MINUTE\r\n" + 
				"			),'%Y-%m-%d %H:%i:%s') AS S1PreArrivalTime,\r\n" + 
				"			departCity.CityName AS S1DepartCityName,\r\n" + 
				"			departCity.CityCode AS S1DepartCityCode,\r\n" + 
				"			arriveCity.CityName AS S1ArriveCityName,\r\n" + 
				"			arriveCity.CityCode AS S1ArriveCityCode,\r\n" + 
				"			ScheduleId AS S1ScheduleId\r\n" + 
				"		FROM\r\n" + 
				"			`schedule`\r\n" + 
				"		LEFT JOIN route ON `schedule`.RouteId = route.RouteId\r\n" + 
				"		LEFT JOIN airport AS departAirport ON route.DepartureAirportIATA = departAirport.IATACode\r\n" + 
				"		LEFT JOIN city AS departCity ON departAirport.CityCode = departCity.CityCode\r\n" + 
				"		LEFT JOIN airport AS arriveAirport ON route.ArrivalAirportIATA = arriveAirport.IATACode\r\n" + 
				"		LEFT JOIN city arriveCity ON arriveAirport.CityCode = arriveCity.CityCode\r\n" + 
				"		LEFT JOIN aircraft ON `schedule`.AircraftId = aircraft.AircraftId\r\n" + 
				"		WHERE\r\n" + 
				"			departCity.CityName = ?\r\n" + 
				"		AND (Date BETWEEN ? and ?)\r\n" + 
				"	) t1,\r\n" + 
				"	(\r\n" + 
				"		SELECT\r\n" + 
				"			date_format(Date,'%Y-%m-%d %H:%i:%s') AS S2Date,\r\n" + 
				"			date_format(Time,'%H:%i:%s') AS S2Time,\r\n" + 
				"			FlightNumber AS S2FlightNumber,\r\n" + 
				"			FlightTime AS S2FlightTime,\r\n" + 
				"			FirstSeatsAmount AS S2FirstSeatsAmount,\r\n" + 
				"			BusinessSeatsAmount AS S2BusinessSeatsAmount,\r\n" + 
				"			EconomySeatsAmount AS S2EconomySeatsAmount,\r\n" + 
				"			EconomyPrice AS S2EconomyPrice,\r\n" + 
				"			date_format(DATE_ADD(\r\n" + 
				"				Date,\r\n" + 
				"				INTERVAL (route.FlightTime) MINUTE\r\n" + 
				"			),'%Y-%m-%d %H:%i:%s') AS S2PreArrivalTime,\r\n" + 
				"			departCity.CityName AS S2DepartCityName,\r\n" + 
				"			departCity.CityCode AS S2DepartCityCode,\r\n" + 
				"			arriveCity.CityName AS S2ArriveCityName,\r\n" + 
				"			arriveCity.CityCode AS S2ArriveCityCode,\r\n" + 
				"			ScheduleId AS S2ScheduleId\r\n" + 
				"		FROM\r\n" + 
				"			`schedule`\r\n" + 
				"		LEFT JOIN route ON `schedule`.RouteId = route.RouteId\r\n" + 
				"		LEFT JOIN airport AS departAirport ON route.DepartureAirportIATA = departAirport.IATACode\r\n" + 
				"		LEFT JOIN city AS departCity ON departAirport.CityCode = departCity.CityCode\r\n" + 
				"		LEFT JOIN airport AS arriveAirport ON route.ArrivalAirportIATA = arriveAirport.IATACode\r\n" + 
				"		LEFT JOIN city arriveCity ON arriveAirport.CityCode = arriveCity.CityCode\r\n" + 
				"		LEFT JOIN aircraft ON `schedule`.AircraftId = aircraft.AircraftId\r\n" + 
				"		WHERE\r\n" + 
				"			arriveCity.CityName = ?\r\n" + 
				"	) t2\r\n" + 
				"WHERE\r\n" + 
				"	t1.S1ArriveCityName = t2.S2DepartCityName\r\n" + 
				"AND S1DepartCityName != S2ArriveCityName\r\n" + 
				"AND TIMESTAMPDIFF(HOUR, t1.S1Date, t2.S2Date) >= 2\r\n" + 
				"AND TIMESTAMPDIFF(HOUR, t1.S1Date, t2.S2Date) <= 9";
		List<HashMap<String, Object>> listSchedule=MySqlHelper.executeQueryReturnMap(sql, new Object[] {fromCity,startDepartureDate,endDepartureDate,toCity});
		return listSchedule;
	}
}
